﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Collections;
namespace mealplanner.database
{

     public static class MenuDB
    {
        public static string comm;


        /// <summary>
        /// this function to add new menu name without its meal in the table meun in database .
        /// </summary>
        /// <param name="name"></param>
        /// <param name="date"></param>
        public static void addMenu(string name, string date, string structure)
        {
            try
            {
                comm = "INSERT INTO `mealplanner`.`menu`(`menu_name`,`date`,`structure`)VALUES(\'" + name + "\',\'" + date + "\',\'" + structure + "\')";
                MySqlDataReader reader = Database.executeCommand(comm);
            }
            catch (Exception e1)
            {
                Console.WriteLine("error in insertion" + e1.Message);
            }
        }

        /// <summary>
        /// get menu meals names by menu id 
        /// </summary>
        /// <param name="menuId"> the menu id</param>
        /// <returns> return list of menu meals </returns>
        public static ArrayList getMenuMeals(int menuId)
        {
            ArrayList list = new ArrayList();
            ArrayList list2 = new ArrayList();
            comm = "select meal_id from mealplanner.meal_menu where mealplanner.meal_menu.menu_id =" + menuId;
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["meal_id"]);
            }
            Database.con.Close();
            foreach (var x in list) {
                comm = "select meal_name from mealplanner.meal where mealplanner.meal.meal_id =" + x;
                reader = Database.executeCommand(comm);
                while (reader.Read())
                {
                    list2.Add(reader["meal_name"]);
                }
                Database.con.Close();
            }
            return list2;
        }

        /// <summary>
        /// this method to add the meals of the menu in the table meal_menu in database. 
        /// </summary>
        /// <param name="menuId"></param>
        /// <param name="mealsId"></param>
        public static void addMenuMeals(int menuId, ArrayList mealsId)
        {
            try
            {
                for (int i = 0; i < mealsId.Count; i++)
                {
                    comm = "INSERT INTO `mealplanner`.`meal_menu`(`meal_id`,`menu_id`)VALUES(" + int.Parse(mealsId[i].ToString()) + "," + menuId + ")";
                    MySqlDataReader reader = Database.executeCommand(comm);
                }
            }
            catch (Exception e1)
            {
                Console.WriteLine("error in insertion" + e1.Message);
            }
        }

        /// <summary>
        /// delete menu by id
        /// </summary>
        /// <param name="menuId"> menu id </param>
        public static void deleteMenuById(int menuId)
        {
            try
            {
                comm = " DELETE FROM `mealplanner`.`menu` WHERE menu_id =" + menuId;
                MySqlDataReader reader = Database.executeCommand(comm);

            }
            catch (Exception e1)
            {
                Console.WriteLine("error in delete menu" + e1.Message);
            }
            Database.con.Close();
        }

        /// <summary>
        /// delete menu by name
        /// </summary>
        /// <param name="menuId"> menu id </param>
        public static void deleteMenuByName(string menuName)
        {
            try
            {
                comm = " DELETE FROM `mealplanner`.`menu` WHERE menu_name =\'" + menuName + "\'";
                MySqlDataReader reader = Database.executeCommand(comm);

            }
            catch (Exception e1)
            {
                Console.WriteLine("error in delete menu" + e1.Message);
            }
            Database.con.Close();
        }

        /// <summary>
        /// method to return menu id by menu name
        /// </summary>
        /// <param name="name"> menu name </param>
        /// <returns></returns>
        public static ArrayList getMenuIdByMenuName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select menu_id from mealplanner.menu where menu_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["menu_id"]);
            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// method to return all menu names and dates .
        /// </summary>
        /// <returns></returns>
        public static ArrayList getAllMenus()
        {
            ArrayList list = new ArrayList();
            comm = "select menu_name from mealplanner.menu";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["menu_name"]);
            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// get menu structure by menu name .
        /// </summary>
        /// <param name="name"> menu name</param>
        /// <returns></returns>
        public static ArrayList getMenuStructureByMenuName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select structure from mealplanner.menu where menu_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["structure"]);
            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// delete one meal from menu by id .
        /// </summary>
        /// <param name="mealId"> meal id </param>
        /// <param name="menuId"> menu id </param>
        public static void deleteMealFromMenuById(int mealId, int menuId)
        {
            try
            {
                comm = "DELETE FROM `mealplanner`.`meal_menu` WHERE meal_id =" + mealId;
                string x = comm + " and menu_id = " + menuId;
                MySqlDataReader reader = Database.executeCommand(x);

            }
            catch (ArgumentException e1)
            {
                Console.WriteLine("error in delete meal from menu" + e1.Message);
            }
            Database.con.Close();
        }

        public static void updateMenuStructure(int menuId,string structure) { 
       // UPDATE `mealplanner`.`menu` SET `structure`='Week 2Day 12Day 2' WHERE `menu_id`='14';
            try
            {
                comm = "update `mealplanner`.`menu` set structure= '" + structure;
                string x = comm + "' where `menu_id`= " + menuId;
                MySqlDataReader reader = Database.executeCommand(x);
            }
            catch (Exception e1)
            {
                Console.WriteLine("error in update structure from menu" + e1.Message);
            }
            Database.con.Close();
        }


    }
}
